﻿using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Net.Http.Headers;
using System.Text;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using OfficeOpenXml;
using xlxsToJsonString.Models;

namespace xlxsToJsonString.Controllers
{
    public class HomeController : Controller
    {
        private readonly IHostingEnvironment hostingEnvironment;
        private readonly GeneralDBContext generalDBContext;

        public HomeController(IHostingEnvironment hostingEnvironment, GeneralDBContext generalDBContext)
        {
            this.hostingEnvironment = hostingEnvironment;
            this.generalDBContext = generalDBContext;
        }

        /// <summary>
        /// 导入xlsx文件
        /// </summary>
        /// <returns></returns>
        public IActionResult Index()
        {
            return View();
        }


        /// <summary>
        /// 导出
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public IActionResult Export()
        {
            string sWebRootFolder = hostingEnvironment.WebRootPath;
            string sFileName = $"{Guid.NewGuid()}.xlsx";
            FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
            using (ExcelPackage package = new ExcelPackage(file))
            {
                // 添加worksheet
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("aspnetcore");
                //添加头
                worksheet.Cells[1, 1].Value = "ID";
                worksheet.Cells[1, 2].Value = "Name";
                worksheet.Cells[1, 3].Value = "Url";
                //添加值
                worksheet.Cells["A2"].Value = 1000;
                worksheet.Cells["B2"].Value = "LineZero";
                worksheet.Cells["C2"].Value = "http://www.cnblogs.com/linezero/";

                worksheet.Cells["A3"].Value = 1001;
                worksheet.Cells["B3"].Value = "LineZero GitHub";
                worksheet.Cells["C3"].Value = "https://github.com/linezero";
                worksheet.Cells["C3"].Style.Font.Bold = true;

                //跨行跨列
                //worksheet.Cells["A1:C1"].Merge = true;
                worksheet.Cells[2, 1, 3, 1].Merge = true;



                package.Save();
            }

          


            return File(sFileName, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        }

        public class Person
        {
            public string Name { get; set; }
            public List<string> list { get; set; }
            public string Sex { get; set; }
           
        }


        [HttpPost]
        public IActionResult Import(IFormFile excelfile)
        {
            string sWebRootFolder = hostingEnvironment.WebRootPath;
            string sFileName = $"{Guid.NewGuid()}.xlsx";
            FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
            try
            {
                using (FileStream fs = new FileStream(file.ToString(), FileMode.Create))
                {
                    excelfile.CopyTo(fs);
                    fs.Flush();
                }
                using (ExcelPackage package = new ExcelPackage(file))
                {
                    List<mapModel> mapList = new List<mapModel>();
                    ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
                    int rowCount = worksheet.Dimension.Rows;
                    int ColCount = worksheet.Dimension.Columns;
                    //bool bHeaderRow = true;
                    for (int row = 3; row <= rowCount; row++)
                    {
                        //for (int col = 1; col <= 5; col++)
                        //{
                        //    if (bHeaderRow)
                        //    {
                        //        sb.Append(worksheet.Cells[row, col].Value.ToString() + "\t");
                        //    }
                        //    else
                        //    {
                        //        sb.Append(worksheet.Cells[row, col].Value.ToString() + "\t");
                        //    }
                        //}
                        //sb.Append(Environment.NewLine);
                        mapModel model = new mapModel();
                        model.code = worksheet.Cells[row, 1].Value.ToString();
                        model.parentcode = worksheet.Cells[row, 2].Value.ToString();
                        model.depth = Convert.ToInt32(worksheet.Cells[row, 4].Value.ToString());
                        model.name = worksheet.Cells[row, 5].Value.ToString();
                        mapList.Add(model);

                        generalDBContext.mapInfo.Add(new mapInfo() { 
                             code = model.code,
                              depth = model.depth,
                               name = model.name,
                                parentcode = model.parentcode
                        
                        });
                    }
                    generalDBContext.SaveChanges();
                    return Ok(mapList);
                }
            }
            catch (Exception ex)
            {
                return Content(ex.Message);
            }
        }


        

        public IActionResult Privacy()
        {
            return View();
        }

        [ResponseCache(Duration = 0, Location = ResponseCacheLocation.None, NoStore = true)]
        public IActionResult Error()
        {
            return View(new ErrorViewModel { RequestId = Activity.Current?.Id ?? HttpContext.TraceIdentifier });
        }
    }
}
